﻿Imports System.Data.SqlClient

Public Class cMainCase

    Dim con As New SqlConnection(strcon)

    'For View
    Public Function select_MainCase(ByVal LawyerID As Long) As DataTable
        Dim dt As New DataTable
        Dim oMain As New cMain
        dt = oMain.getData("select m.MainCaseID,m.MainCaseName,c.CaseCategoryName,m.LawyerID,m.UserID from tblMainCase m INNER JOIN tblCaseCategory c ON m.CaseCategoryID=c.CaseCategoryID WHERE LawyerID=N'" & LawyerID & "'")
        Return dt
    End Function
    'End For View

    'For View at Entry Form
    Public Function select_MainCase_byID(ByVal ID As Long) As DataTable
        Dim dt As New DataTable
        Dim oMain As New cMain
        dt = oMain.getData("SELECT * FROM tblMainCase WHERE MainCaseID=N'" & ID & "'")
        Return dt
    End Function
    'End For View at Entry Form

    'For Search
    Public Function select_MainCase_byKeyword(ByVal LawyerID As Long, ByVal Keyword As String) As DataTable
        Dim dt As New DataTable
        Dim oMain As New cMain
        dt = oMain.getData("select m.MainCaseID,m.MainCaseName,c.CaseCategoryName,m.LawyerID,m.UserID from tblMainCase m INNER JOIN tblCaseCategory c ON m.CaseCategoryID=c.CaseCategoryID WHERE LawyerID=N'" & LawyerID & "' AND LOWER(m.MainCaseName + c.CaseCategoryName) LIKE N'%' + N'" & LCase(Keyword) & "' + N'%'")
        Return dt
    End Function
    'End Search

    Public Function insert_MainCase_ID(ByVal MainCaseID As Long) As Boolean
        Dim rv As Boolean = True
        Dim cmd As New SqlCommand
        cmd.CommandText = "INSERT INTO tblMainCase(MainCaseID) VALUES(@MainCaseID)"
        With cmd.Parameters
            .AddWithValue("@MainCaseID", MainCaseID)
        End With
        cmd.Connection = con
        Try
            con.Open()
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            rv = False
        Finally
            con.Close()
            cmd.Dispose()
        End Try
        Return rv
    End Function

    Public Function delete_MainCase_ID(ByVal MainCaseID As Long) As Boolean
        Dim rv As Boolean = True
        Dim cmd As New SqlCommand
        cmd.CommandText = "DELETE FROM tblMainCase WHERE MainCaseID=@MainCaseID"
        With cmd.Parameters
            .AddWithValue("@MainCaseID", MainCaseID)
        End With
        cmd.Connection = con
        Try
            con.Open()
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            rv = False
        Finally
            con.Close()
            cmd.Dispose()
        End Try
        Return rv
    End Function

End Class
